<?php

namespace App\Http\Controllers\Admin;

use App\Models\Admin;
use App\Models\Users;
use App\Services\ModuleService;
use App\Services\StatService;
use App\Services\UserService;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class StatController extends Controller
{
    private $userService;

    private $statService;

    private $request;

    private const MONTH_CN = [
        1   => '一月',
        2   => '二月',
        3   => '三月',
        4   => '四月',
        5   => '五月',
        6   => '六月',
        7   => '七月',
        8   => '八月',
        9   => '九月',
        10   => '十月',
        11   => '十一月',
        12   => '十二月',
    ];

    public function __construct(UserService $userService, StatService $statService, Request $request)
    {
        $this->statService = $statService;
        $this->userService = $userService;
        $this->request = $request;
    }

    public function getPage(string $title, string $pageName, string $url = '')
    {
        return [
            'page_name' => $pageName,
            'title' => $title,
            'breadcrumb' => [
                [
                    'text' => '数据报表',
                    'url' => $url
                ]
            ],
        ];
    }

    public function brandStat()
    {
        $carBrandTotalStat =
            DB::table('xpel_car_brand_stat')
            ->where('create_date', '>=', date('Y') . '-01')
            ->groupBy('car_brand')
            ->select(DB::raw('count(*) as brand_count'))
            ->get();
        $brand_total_cnt = count($carBrandTotalStat);
        $max = 0;
        foreach ($carBrandTotalStat as $item) {
            $max = $item->brand_count > $max ? $item->brand_count : $max;
        }

        $lastYear = (int)date('Y') - 1;
        $oldCarBrandTotalStat = DB::table('xpel_car_brand_stat')
            ->where('create_date', '>=', $lastYear . '-01')
            ->where('create_date', '<=', $lastYear . '-' . date('m-d'))
            ->groupBy('car_brand')
            ->select(DB::raw('count(*) as brand_count'))
            ->get();
        $brand_old_total_stat = count($oldCarBrandTotalStat);
        $brand_total_rate = $brand_total_cnt > 0 ? round(($brand_total_cnt - $brand_old_total_stat) / $brand_total_cnt * 100, 2) : 0;
        $oldMax = 0;
        foreach ($oldCarBrandTotalStat as $item) {
            $oldMax = $item->brand_count > $oldMax ? $item->brand_count : $oldMax;
        }
        $brand_rate = $oldMax > 0 ? round(($max - $oldMax) / $oldMax * 100, 2) : 0;

        $oldCarBrandHbTotalStat = DB::table('xpel_car_brand_stat')
            ->where('create_date', '>=', $this->getHbDate()['start_date'])
            ->where('create_date', '<=', $this->getHbDate()['end_date'])
            ->groupBy('car_brand')
            ->select(DB::raw('count(*) as brand_count'))
            ->get();
        $brand_old_total_hb_stat = count($oldCarBrandHbTotalStat);
        $brand_total_hb_rate = $brand_total_cnt > 0 ? round(($brand_old_total_hb_stat - $brand_total_cnt) / $brand_total_cnt * 100, 2) : 0;
        $oldHbMax = 0;
        foreach ($oldCarBrandTotalStat as $item) {
            $oldHbMax = $item->brand_count > $oldHbMax ? $item->brand_count : $oldHbMax;
        }
        $brand_hb_rate = $oldHbMax > 0 ? round(($max - $oldHbMax) / $oldHbMax * 100, 2) : 0;

        $carModelStat = DB::table('xpel_car_brand_stat')
            ->where('create_date', '>=', date('Y') . '-01')
            ->groupBy('car_model')
            ->select(DB::raw('count(*) as model_count'))
            ->get();

        $modelMax = 0;
        foreach ($carModelStat as $item) {
            $modelMax = $item->model_count > $modelMax ? $item->model_count : $modelMax;
        }

        $oldCarBrandStat = DB::table('xpel_car_brand_stat')
            ->where('create_date', '>=', $lastYear . '-01')
            ->where('create_date', '<=', $lastYear . '-' . date('m-d'))
            ->groupBy('car_model')
            ->select(DB::raw('count(*) as model_count'))
            ->get();

        $oldModelMax = 0;
        foreach ($oldCarBrandStat as $item) {
            $oldModelMax = $item->model_count > $modelMax ? $item->model_count : $oldModelMax;
        }
        $model_rate = $oldModelMax > 0 ? round(($modelMax - $oldModelMax) / $oldModelMax * 100, 2) : 0;

        $oldCarBrandHubStat = DB::table('xpel_car_brand_stat')
            ->where('create_date', '>=', $this->getHbDate()['start_date'])
            ->where('create_date', '<=', $this->getHbDate()['end_date'])
            ->groupBy('car_model')
            ->select(DB::raw('count(*) as model_count'))
            ->get();

        $oldModelHubMax = 0;
        foreach ($oldCarBrandHubStat as $item) {
            $oldModelHubMax = $item->model_count > $oldModelHubMax ? $item->model_count : $oldModelHubMax;
        }
        $model_hb_rate = $oldModelHubMax > 0 ? round(($modelMax - $oldModelHubMax) / $modelMax * 100, 2) : 0;

        $brands = DB::table('xpel_car_brand_stat')
            ->select('car_brand')
            ->groupBy('car_brand')
            ->get();

        $data = [
            'brand_total_stat' => (int)$brand_total_cnt,
            'brand_total_rate' => $brand_total_rate,
            'brand_total_hb_rate' => $brand_total_hb_rate,
            'brand_stat'       => (int)$max,
            'brand_rate'       => $brand_rate,
            'brand_hb_rate'       => $brand_hb_rate,
            'model_stat'       => $modelMax,
            'model_rate'       => $model_rate,
            'model_hb_rate'       => $model_hb_rate,
            'distributions'    => $this->getDistributions(),
            'brands'           => $brands,
            'year'             => date('Y'),
            'page'             => $this->getPage('车辆品牌统计', 'stat_brand'),
        ];

        return view('admin.stat.brand', $data);
    }

    public function getDistributions()
    {
        return Admin::where('status', 1)
            ->where('user_type', 2)
            ->orderBy('status')
            ->orderBy('unique_id')
            ->select(['id', 'abbr'])
            ->get();
    }

    public function brandRank(Request $request)
    {
        $year = $request->get('year');
        $month = $request->get('month');
        $dealer_id = $request->get('dealer_id');
        $dist_id = $request->get('dist_id');

        if (empty($year)) {
            return response()->json(
                [
                    'code'  => 0,
                    'list'  => [],
                ]
            );
        }

        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT count(1) AS brand_count, car_brand FROM xpel_car_brand_stat WHERE create_date>="' . $start_date . '"';
        $sql .= ' AND create_date <"' . $end_date . '"';

        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }

        if (!empty($dist_id)) {
            $sql .= ' AND dist_id=' . $dist_id;
        }

        $sql .= ' GROUP BY car_brand';
        $sql .= ' ORDER BY brand_count DESC LIMIT 20';
        $carBrandTotalStat = DB::select($sql);

        return response()->json(
            [
                'code'  => 1,
                'list'  => $carBrandTotalStat,
            ]
        );
    }

    public function modelStat(Request $request)
    {
        $year = $request->get('year');
        $month = $request->get('month');
        $dealer_id = $request->get('dealer_id');
        $dist_id = $request->get('dist_id');
        $car_brand = $request->get('car_brand');

        if (empty($year)) {
            return response()->json(
                [
                    'code'  => 0,
                    'list'  => [],
                ]
            );
        }

        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT count(1) AS model_count, car_model FROM xpel_car_brand_stat WHERE create_date>="' . $start_date . '"';
        $sql .= ' AND create_date <"' . $end_date . '"';

        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }

        if (!empty($dist_id)) {
            $sql .= ' AND dist_id=' . $dist_id;
        }

        if (!empty($car_brand)) {
            $sql .= ' AND car_brand="' . $car_brand . '"';
        }

        $sql .= ' GROUP BY car_model';
        $sql .= ' ORDER BY model_count DESC LIMIT 10';
        $carBrandTotalStat = DB::select($sql);

        return response()->json(
            [
                'code'  => 1,
                'list'  => $carBrandTotalStat,
            ]
        );
    }

    public function rollStat()
    {
        $data = [
            'distributions' => $this->getDistributions(),
            'page'          => $this->getPage('膜卷号数据统计', 'stat_roll'),
            'year'          => date('Y'),
        ];

        return view('admin.stat.roll', $data);
    }

    public function rollSaleStat(Request $request)
    {
        $year = $request->get('year');
        $dealer_id = $request->get('dealer_id');
        $dist_id = $request->get('dist_id');
        $month = $request->get('month');

        if (empty($year)) {
            return response()->json(
                [
                    'code'  => 0,
                    'list'  => [],
                ]
            );
        }

        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
            $months = [self::MONTH_CN[(int)$month]];
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
            $months = ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'];
        }

        $sql = 'SELECT sum(sale_1) + sum(sale_2) + sum(sale_3) as sale_sum, 
        sum(transfer_1) + sum(transfer_2) + sum(transfer_3) as transfer_sum,
        sum(return_1) + sum(return_2) + sum(return_3) as return_sum,
        sum(stock_1) + sum(stock_2) + sum(stock_3) as stock_sum, DATE_FORMAT(create_date,"%m") months FROM xpel_roll_stat WHERE create_date>="' . $start_date . '"';
        $sql .= ' AND create_date <"' . $end_date . '"';

        if (!empty($dealer_id)) {
            $sql .= ' AND user_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $dealer_ids = $this->userService->getDealers([$dist_id]);
            if (!empty($dealer_ids)) {
                return ' AND user_id IN (' . implode(',', $dealer_ids)  . ')';
            }
        }
        $sql .= ' GROUP BY months';

        $saleStat = DB::select($sql);

        $saleSum = [];
        $transferSum = [];
        $returnSum = [];
        $stockSum = [];

        foreach ($saleStat as $item) {
            $saleSum[] = (int)($item->sale_sum ?: 0);
            $transferSum[] = (int)$item->transfer_sum ?: 0;
            $returnSum[] = (int)$item->return_sum ?: 0;
            $stockSum[] = (int)$item->stock_sum ?: 0;
        }

        $result = [
            [
                'name'  => '库存量',
                'data'  => $stockSum,
            ],
            [
                'name'  => '销售量',
                'data'  => $saleSum,
            ],
            [
                'name'  => '调货量',
                'data'  => $transferSum,
            ],
            [
                'name'  => '退货量',
                'data'  => $returnSum,
            ],
        ];

        return response()->json(
            [
                'code'  => 1,
                'list'  => $result,
                'months' => $months,
            ]
        );
    }

    public function rollQm(Request $request)
    {
        $year = $request->get('year');
        $dealer_id = $request->get('dealer_id');
        $dist_id = $request->get('dist_id');
        $month = $request->get('month');

        if (empty($year)) {
            return response()->json(
                [
                    'code'  => 0,
                    'list'  => [],
                ]
            );
        }

        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
            $months = [self::MONTH_CN[(int)$month]];
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
            $months = ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'];
        }

        $sql = 'SELECT sum(sale_1) as sale_sum, 
        sum(transfer_1) as transfer_sum,
        sum(return_1) as return_sum,
        DATE_FORMAT(create_date,"%m") months FROM xpel_roll_stat WHERE create_date>="' . $start_date . '"';
        $sql .= ' AND create_date <"' . $end_date . '"';

        if (empty($dealer_id) && empty($dist_id)) {
            $sql .= ' AND user_id=1';
        }

        if (!empty($dealer_id)) {
            $sql .= ' AND user_id=' . $dealer_id;
        } elseif (!empty($dist_id)) {
            $sql .= ' AND user_id=' . $dist_id;
        }

        $sql .= ' GROUP BY months';

        $saleStat = DB::select($sql);

        $saleSum = [];
        $transferSum = [];
        $returnSum = [];

        foreach ($saleStat as $item) {
            $saleSum[] = round($item->sale_sum ?: 0, 2);
            $transferSum[] = round($item->transfer_sum ?: 0, 2);
            $returnSum[] = round($item->return_sum ?: 0, 2);
        }

        $result = [
            [
                'name'  => '销售量',
                'data'  => $saleSum,
            ],
            [
                'name'  => '调货量',
                'data'  => $transferSum,
            ],
            [
                'name'  => '退货量',
                'data'  => $returnSum,
            ],
        ];

        return response()->json(
            [
                'code'  => 1,
                'list'  => $result,
                'months' => $months,
            ]
        );
    }

    public function rollWindow(Request $request)
    {
        $year = $request->get('year');
        $dealer_id = $request->get('dealer_id');
        $dist_id = $request->get('dist_id');
        $month = $request->get('month');

        if (empty($year)) {
            return response()->json(
                [
                    'code'  => 0,
                    'list'  => [],
                ]
            );
        }

        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
            $months = [self::MONTH_CN[(int)$month]];
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
            $months = ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'];
        }

        $sql = 'SELECT sum(sale_2) as sale_sum, 
        sum(transfer_2) as transfer_sum,
        sum(return_2) as return_sum,
        DATE_FORMAT(create_date,"%m") months FROM xpel_roll_stat WHERE create_date>="' . $start_date . '"';
        $sql .= ' AND create_date <"' . $end_date . '"';

        if (empty($dealer_id) && empty($dist_id)) {
            $sql .= ' AND user_id=1';
        }

        if (!empty($dealer_id)) {
            $sql .= ' AND user_id=' . $dealer_id;
        } elseif (!empty($dist_id)) {
            $sql .= ' AND user_id=' . $dist_id;
        }
        $sql .= ' GROUP BY months';

        $saleStat = DB::select($sql);

        $saleSum = [];
        $transferSum = [];
        $returnSum = [];
        $stockSum = [];

        foreach ($saleStat as $item) {
            $saleSum[] = round($item->sale_sum ?: 0, 2);
            $transferSum[] = round($item->transfer_sum ?: 0, 2);
            $returnSum[] = round($item->return_sum ?: 0, 2);
        }

        $result = [
            [
                'name'  => '销售量',
                'data'  => $saleSum,
            ],
            [
                'name'  => '调货量',
                'data'  => $transferSum,
            ],
            [
                'name'  => '退货量',
                'data'  => $returnSum,
            ],
        ];

        return response()->json(
            [
                'code'  => 1,
                'list'  => $result,
                'months' => $months,
            ]
        );
    }

    public function storeStat()
    {
        $saleTotal = DB::table('xpel_user_sales')
            ->where('create_date', '>=', date('Y') . '-01')
            ->sum('sales_num');

        $lastYear = (int)date('Y') - 1;
        $statSaleTotal = DB::table('xpel_user_sales')
            ->where('create_date', '>=', $lastYear . '-01')
            ->where('create_date', '<=', $lastYear . '-' . date('m-d'))
            ->sum('sales_num');

        $saleDistributionTotal = DB::table('xpel_user_sales')
            ->where('create_date', '>=', date('Y') . '-01')
            ->where('user_type', 2)
            ->groupBy('user_id')
            ->select(DB::raw('sum(sales_num) as sale_count'))
            ->get();

        $max = 0;
        foreach ($saleDistributionTotal as $item) {
            $max = $max > $item->sale_count ? $max : $item->sale_count;
        }

        $oldSaleDistributionTotal = DB::table('xpel_user_sales')
            ->where('create_date', '>=', $lastYear . '-01')
            ->where('create_date', '<=', $lastYear . '-' . date('m-d'))
            ->where('user_type', 2)
            ->groupBy('user_id')
            ->select(DB::raw('sum(sales_num) as sale_count'))
            ->get();

        $oldMax = 0;
        foreach ($oldSaleDistributionTotal as $item) {
            $oldMax = $oldMax > $item->sale_count ? $oldMax : $item->sale_count;
        }
        $max_rate = $max > 0 ? round(($max - $oldMax) / $max * 100, 2) : 0;

        $saleDealerTotal = DB::table('xpel_user_sales')
            ->where('create_date', '>=', date('Y') . '-01')
            ->where('user_type', 3)
            ->groupBy('user_id')
            ->select(DB::raw('sum(sales_num) as sale_count'))
            ->get();

        $dealerMax = 0;
        foreach ($saleDealerTotal as $item) {
            $dealerMax = $dealerMax > $item->sale_count ? $dealerMax : $item->sale_count;
        }

        $oldSaleDealerTotal = DB::table('xpel_user_sales')
            ->where('create_date', '>=', $lastYear . '-01')
            ->where('create_date', '<=', $lastYear . '-' . date('m-d'))
            ->where('user_type', 3)
            ->groupBy('user_id')
            ->select(DB::raw('sum(sales_num) as sale_count'))
            ->get();

        $oldDealerMax = 0;
        foreach ($oldSaleDealerTotal as $item) {
            $oldDealerMax = $oldDealerMax > $item->sale_count ? $oldDealerMax : $item->sale_count;
        }
        $max_dealer_rate = $dealerMax > 0 ? round(($dealerMax - $oldDealerMax) / $dealerMax * 100, 2) : 0;

        $sql = 'SELECT sum(score) as max_score FROM xpel_dealer_stat WHERE create_month>="' . date('Y') . '-01" GROUP BY dealer_id ORDER BY max_score DESC LIMIT 1';
        $max_store_score = DB::select($sql);
        $max_store_score = empty($max_store_score) ? 0 : $max_store_score[0]->max_score;

        $sql = 'SELECT sum(score) as max_score FROM xpel_dealer_stat WHERE create_month>="' . $lastYear . '-01" AND create_month<"' . $lastYear . '-' . date('m-d') . '" GROUP BY dealer_id ORDER BY max_score DESC LIMIT 1';
        $max_old_store_score = DB::select($sql);
        $max_old_store_score = empty($max_old_store_score) ? 0 : $max_old_store_score[0]->max_score;
        $max_store_rate = $max_store_score > 0 ? round(($max_store_score - $max_old_store_score) / $max_store_score * 100, 2) : 0;

        $data = [
            'sale_total'    => (int)$saleTotal,
            'sale_rate'     => $saleTotal > 0 ? round(($saleTotal - $statSaleTotal) / $statSaleTotal, 2) * 100 : 0,
            'distribution_max' => (int)$max,
            'distribution_rate' => $max_rate,
            'dealer_max'    => $dealerMax,
            'dealer_rate'   => $max_dealer_rate,
            'store_max'     => $max_store_score,
            'store_rate'    => $max_store_rate,
            'distributions' => $this->getDistributions(),
            'page'          => $this->getPage('全国销售排名', 'stat_store'),
        ];

        return view('admin.stat.store', $data);
    }

    /**
     * 全国销售情况
     *
     * @param Request $request
     * @return JsonResponse
     */
    public function allSales(Request $request)
    {
        $year = $request->get('year');

        $result = [
            [
                'name'  => $year,
                'data'  => $this->getYearSales($request, $year . '-01', ($year + 1) .  '-01'),
            ],
        ];

        return response()->json([
            'code'  => 1,
            'list'  => $result
        ]);
    }

    public function getYearSales(Request $request, string $startDate, string $endDate)
    {
        $sql = 'SELECT SUM(num) AS sale_total, create_month FROM xpel_dealer_roll_stat WHERE create_month>="%s" AND create_month<="%s" AND dealer_id=1';
        $sql = sprintf($sql, $startDate, $endDate);
        $sql .= $this->getSql($request);
        $sql .= ' GROUP BY create_month ORDER BY create_month ASC';
        $newData = DB::select($sql);
        $data = [];
        foreach ($newData as $item) {
            $data[] = round($item->sale_total, 2);
        }

        return $data;
    }

    /**
     * 组装sql
     *
     * @param Request $request
     * @return string
     */
    public function getSql(Request $request): string
    {
        $dealer_id = $request->input('dealer_id');
        $dist_id = $request->input('dist_id');

        $sql = '';
        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND dist_id=' . $dist_id . ' AND dist_id=dealer_id';
        }

        return $sql;
    }

    public function dealerRank(Request $request)
    {
        $year = $request->get('year');
        $month = $request->get('month');
        $type = $request->get('type');
        $page = $request->get('page');
        $dist_id = $request->get("dist_id");

        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }
        $select = $type == 1 ? 'qm_score' : 'win_score';
        $value = $type == 1 ? 'dealer.task_value as value' : 'dealer.win_task_value as value';

        $sql = 'SELECT sum(' . $select . ') as total_score, users.abbr, ' . $value . ' FROM xpel_dealer_stat LEFT JOIN users ON xpel_dealer_stat.dealer_id=users.id LEFT JOIN dealer ON dealer.user_id=xpel_dealer_stat.dealer_id WHERE create_month>="' . $start_date . '" AND create_month<"' . $end_date . '" AND dealer_id!=dist_id AND dealer_id!=1 AND dist_id!=1';
        if (!empty($dist_id)) {
            $sql .= ' AND dist_id=' . $dist_id;
        }
        $sql .= ' GROUP BY dealer_id ORDER BY total_score DESC';
        if (empty($page) || $page !== 'all') {
            $sql .= ' LIMIT 20';
        }
        $dealers = DB::select($sql);

        $list = [];
        if (!empty($dealers) && is_array($dealers)) {
            foreach ($dealers as $dealer) {
                $percent = !empty($dealer->value) && $dealer->value > 0 ? round($dealer->total_score / $dealer->value * 100, 2) : 0;
                $list[] = [
                    'name' => $dealer->abbr,
                    'y' => round($dealer->total_score, 2),
                    'percent' => $percent,
                ];
            }
        }

        return response()->json([
            'code'  => 1,
            'list'  => $list
        ]);
    }

    public function distributionsRank(Request $request)
    {
        $year = $request->get('year');
        $month = $request->get('month');
        $type = $request->get('type');
        $page = $request->get('page');
        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $select = $type == 1 ? 'qm_score' : 'win_score';
        $value = $type == 1 ? 'dealer.task_value as value' : 'dealer.win_task_value as value';

        $sql = 'SELECT sum(' . $select . ') as total_score, users.id as user_id, users.abbr, ' . $value . ' FROM xpel_dealer_stat LEFT JOIN users ON xpel_dealer_stat.dealer_id=users.id LEFT JOIN dealer ON dealer.user_id=xpel_dealer_stat.dealer_id WHERE users.user_type=2 AND create_month>="' . $start_date . '" AND create_month<"' . $end_date . '" GROUP BY dealer_id ORDER BY total_score DESC';
        if (empty($page) || $page !== 'all') {
            $sql .= ' LIMIT 20';
        }
        $dealers = DB::select($sql);

        $list = [];
        if (!empty($dealers) && is_array($dealers)) {
            foreach ($dealers as $dealer) {
                $percent = !empty($dealer->value) && $dealer->value > 0 ? round($dealer->total_score / $dealer->value * 100, 2) : 0;
                $list[] = [
                    'name' => $dealer->abbr,
                    'user_id' => $dealer->user_id,
                    'y' => round($dealer->total_score, 2),
                    'percent' => $percent,
                ];
            }
        }

        return response()->json([
            'code'  => 1,
            'list'  => $list
        ]);
    }

    public function storeTypeStat(Request $request)
    {
        $year = $request->get('year') ?: date('Y');
        $month = $request->get('month');
        $dist_id = $request->get('dist_id');
        $zsdj = $request->get('zsdj') ?? 'all';
        $mdtype = $request->get('mdtype') ?? 'all';

        if (!empty($month)) {
            $start_date = $year  . '-' . $month . '-01';
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date  = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $countSql = 'SELECT COUNT(1) AS cnt FROM users WHERE user_type=3  AND created_at<"' . $end_date . '" AND status=1 AND deleted_at IS NULL ';

        $sql = 'SELECT COUNT(1) AS cnt, mdtype FROM users WHERE user_type=3 AND created_at<"' . $end_date . '" AND status=1 AND deleted_at IS NULL ';
        if (!empty($dist_id)) {
            $sql .= ' AND creator_id=' . $dist_id;
            $countSql .= ' AND creator_id=' . $dist_id;
        }
        if ($mdtype !== 'all') {
            $sql .= ' AND users.mdtype=' . $mdtype;
            $countSql .= ' AND users.mdtype=' . $mdtype;
        }
        if ($zsdj !== 'all') {
            $sql .= ' AND users.zsdj=' . $zsdj;
            $countSql .= ' AND users.zsdj=' . $zsdj;
        }

        $sql .= ' GROUP BY mdtype';
        $data = DB::select($sql);
        $countData = DB::select($countSql);

        $result = [];
        if (!empty($data)) {
            foreach ($data as $item) {
                if ($item->mdtype == 1) {
                    $result[] = [
                        'name' => '外星人店',
                        'y'    => $item->cnt,
                    ];
                }
                if ($item->mdtype == 2) {
                    $result[] = [
                        'name' => '授权店',
                        'y'    => $item->cnt,
                    ];
                }
                if ($item->mdtype == 3) {
                    $result[] = [
                        'name' => '直营店',
                        'y'    => $item->cnt,
                    ];
                }
                if ($item->mdtype == 4) {
                    $result[] = [
                        'name' => '旗舰店',
                        'y'    => $item->cnt,
                    ];
                }
                if ($item->mdtype == 5) {
                    $result[] = [
                        'name' => 'ASP服务商',
                        'y'    => $item->cnt,
                    ];
                }
                if ($item->mdtype == 6) {
                    $result[] = [
                        'name' => '其他',
                        'y'    => $item->cnt,
                    ];
                }
            }
        }

        return response()->json([
            'code'  => 1,
            'list'  => $result,
            'total' => !empty($countData) && !empty($countData[0]) && !empty($countData[0]->cnt) ? $countData[0]->cnt : 0,
        ]);
    }

    public function rollCarOwner(Request $request)
    {
        $sql = 'SELECT count(1) as cnt, province FROM xpel_car_owner GROUP BY province';
        $data = DB::select($sql);

        $result = [];
        if (!empty($data)) {
            foreach ($data as $item) {
                $city = str_replace('省', '', $item->province);
                $city = str_replace('市', '', $city);
                $result[] = ['province' => $city, 'value' => $item->cnt];
            }
        }
        return response()->json([
            'code'  => 1,
            'list'  => $result
        ]);
    }

    public function singleRoll(Request $request)
    {
        $year = $request->input('year');
        $month = $request->input('month');
        $type = $request->input("type");
        $dealer_id = $request->input('dealer_id');
        $dist_id = $request->input('dist_id');

        if (!empty($month)) {
            $start_date = $year  . '-' . $month . '-01';
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date  = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT sum(num) AS total, film_name as name, film_type_id FROM xpel_dealer_roll_stat WHERE create_month>="' . $start_date . '" AND create_month<="' . $end_date  . '" AND product_type_id=' . $type;

        if (empty($dealer_id) && empty($dist_id)) {
            $sql .= " AND dealer_id=1";
        }

        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        } elseif (!empty($dist_id)) {
            $sql .= ' AND dealer_id=' . $dist_id;
        }

        $sql .= ' GROUP BY film_type_id ORDER BY total DESC LIMIT 10';
        $result = DB::select($sql);
        if (empty($result)) {
            return [
                'code'  => 0,
                'list'  => []
            ];
        }

        $roll_names = [];
        $year_data = [];
        foreach ($result as $item) {
            $roll_names[] = $item->name;
            $year_data[] = (int)$item->total;
        }

        $data = [
            [
                'name'  => $year,
                'data'  => $year_data,
            ],
        ];

        return response()->json([
            'code'  => 1,
            'list'  => $data,
            'roll_names' => $roll_names,
        ]);
    }

    public function warrantyStat()
    {
        $start_date = date('Y') . '-01';
        $warrantyTotal = DB::table('warranty')
            ->where('approved_date', '>=', $start_date)
            ->where('approved', 1)
            ->whereNull('deleted_at')
            ->count();

        $lastYear = (int)date('Y') - 1;
        $oldWarrantyTotal = DB::table('warranty')
            ->where('approved_date', '>=', $lastYear . '-01')
            ->where('approved_date', '<=', $lastYear . '-' . date('m-d') . ' 24:00:00')
            ->where('approved', 1)
            ->whereNull('deleted_at')
            ->count();
        $warranty_rate = $oldWarrantyTotal > 0 ?  round(($warrantyTotal - $oldWarrantyTotal) / $oldWarrantyTotal, 2) * 100 : 0;

        $lastYear = (int)date('Y') - 1;
        $hb_end_date = $this->getHbDate()['end_date'];
        $hb_start_date = $this->getHbDate()['start_date'];
        $oldHbWarrantyTotal = DB::table('warranty')
            ->where('approved_date', '>=', $hb_start_date)
            ->where('approved_date', '<=', $hb_end_date)
            ->where('approved', 1)
            ->whereNull('deleted_at')
            ->count();
        $warranty_hb_rate = $oldHbWarrantyTotal > 0 ?  round(($warrantyTotal - $oldHbWarrantyTotal) / $oldHbWarrantyTotal * 100, 2) : 0;

        $roll_1_total = DB::table('warranty', 'w')
            ->whereNull('w.deleted_at')
            ->leftJoin('users as u', 'w.user_id', '=', 'u.id')
            ->leftJoin('warranty_to_vehicle_coverage as wvc', 'w.id', '=', 'wvc.warranty_id')
            ->whereNull('wvc.deleted_at')
            ->leftJoin('new_precut as p', 'w.precut_id', '=', 'p.id')
            ->leftJoin('film_type_master as fm', 'fm.id', 'wvc.film_type_id')
            ->where('approved_date', '>=', $start_date)
            ->where('approved_date', '<=', date("Y-m-d", strtotime('+1 day')))
            ->where('approved', 1)
            ->where('fm.product_type_id', 1)
            ->select(DB::raw('count(distinct(w.id)) as total'))
            ->get();
        $roll_1_total = !empty($roll_1_total) && !empty($roll_1_total[0]->total) ? $roll_1_total[0]->total : 0;
        /*
        $roll_1_total = DB::table('xpel_warranty_stat')
            ->where('create_date', '>=', $start_date)
            ->sum('roll_1');*/

        $old_roll_1_total = DB::table('xpel_warranty_stat')
            ->where('create_date', '>=', $lastYear . '-01')
            ->where('create_date', '<', date('Y') . '-01')
            ->sum('roll_1');
        $roll_1_rate = $old_roll_1_total > 0 ?  round(($roll_1_total - $old_roll_1_total) / $old_roll_1_total * 100, 2) : 0;

        $old_roll_1_hb_total = DB::table('xpel_warranty_stat')
            ->where('create_date', '>=', $hb_start_date)
            ->where('create_date', '<', $hb_end_date)
            ->sum('roll_1');
        $roll_1_hb_rate = $old_roll_1_hb_total > 0 ?  round(($roll_1_total - $old_roll_1_hb_total) / $old_roll_1_hb_total * 100, 2) : 0;

        $roll_2_total = DB::table('warranty', 'w')
            ->whereNull('w.deleted_at')
            ->leftJoin('users as u', 'w.user_id', '=', 'u.id')
            ->leftJoin('warranty_to_vehicle_coverage as wvc', 'w.id', '=', 'wvc.warranty_id')
            ->whereNull('wvc.deleted_at')
            ->leftJoin('new_precut as p', 'w.precut_id', '=', 'p.id')
            ->leftJoin('film_type_master as fm', 'fm.id', 'wvc.film_type_id')
            ->where('approved_date', '>=', $start_date)
            ->where('approved_date', '<=', date("Y-m-d", strtotime('+1 day')))
            ->where('approved', 1)
            ->where('fm.product_type_id', 2)
            ->select(DB::raw('count(distinct(w.id)) as total'))
            ->get();
        $roll_2_total = !empty($roll_2_total) && !empty($roll_2_total[0]->total) ? $roll_2_total[0]->total : 0;

        $old_roll_2_total = DB::table('xpel_warranty_stat')
            ->where('create_date', '>=', $lastYear . '-01')
            ->where('create_date', '<', date('Y') . '-01')
            ->sum('roll_2');
        $roll_2_rate = $old_roll_2_total > 0 ?  round(($roll_2_total - $old_roll_2_total) / $old_roll_2_total * 100, 2) : 0;

        $old_roll_hb_2_total = DB::table('xpel_warranty_stat')
            ->where('create_date', '>=', $lastYear . '-01')
            ->where('create_date', '<', date('Y') . '-01')
            ->sum('roll_2');
        $roll_2_hb_rate = $old_roll_hb_2_total > 0 ?  round(($roll_2_total - $old_roll_hb_2_total) / $old_roll_hb_2_total * 100, 2) : 0;

        $sql = 'SELECT count(w.id) AS total, film_type_id FROM warranty_to_vehicle_coverage wc LEFT JOIN warranty w ON wc.warranty_id=w.id WHERE approved=1 AND approved_date>="' . $start_date  . '" AND w.deleted_at IS NULL AND wc.deleted_at IS NULL  GROUP BY film_type_id ORDER BY total DESC LIMIT 1';
        $roll_warranty_total = DB::select($sql);
        $film_type_id = $roll_warranty_total && $roll_warranty_total[0] && $roll_warranty_total[0]->film_type_id ? $roll_warranty_total[0]->film_type_id : 0;
        $roll_warranty_total = $roll_warranty_total && $roll_warranty_total[0] && $roll_warranty_total[0]->total ? $roll_warranty_total[0]->total : 0;
        $sql = 'SELECT count(w.id) AS total FROM warranty_to_vehicle_coverage wc LEFT JOIN warranty w ON wc.warranty_id=w.id WHERE approved=1 AND approved_date>="' . $lastYear . '-01" AND approved_date<"' . date('Y') . '-01" AND w.deleted_at IS NULL AND wc.deleted_at IS NULL GROUP BY film_type_id ORDER BY total DESC LIMIT 1';
        $old_roll_warranty_total = DB::select($sql);
        $old_roll_warranty_total = $old_roll_warranty_total && $old_roll_warranty_total[0] && $old_roll_warranty_total[0]->total ? $old_roll_warranty_total[0]->total : 0;
        $roll_warranty_rate = $old_roll_warranty_total > 0 ? round(($roll_warranty_total - $old_roll_warranty_total) / $old_roll_warranty_total, 2) * 100 : 0;
        $sql = 'SELECT count(w.id) AS total FROM warranty_to_vehicle_coverage wc LEFT JOIN warranty w ON wc.warranty_id=w.id WHERE approved=1 AND approved_date>="' . $hb_start_date . '" AND approved_date<="' . $hb_end_date . '" AND w.deleted_at IS NULL AND wc.deleted_at IS NULL GROUP BY film_type_id ORDER BY total DESC LIMIT 1';
        $old_roll_hb_warranty_total = DB::select($sql);
        $old_roll_warranty_hb_total = $old_roll_hb_warranty_total && $old_roll_hb_warranty_total[0] && $old_roll_hb_warranty_total[0]->total ? $old_roll_hb_warranty_total[0]->total : 0;
        $roll_warranty_hb_rate = $old_roll_warranty_hb_total > 0 ? round(($roll_warranty_total - $old_roll_warranty_hb_total) / $old_roll_warranty_hb_total * 100, 2) : 0;

        $distributions = Admin::where('status', 1)
            ->where('user_type', 2)
            ->orderBy('status')
            ->orderBy('unique_id')
            ->select(['id', 'abbr'])
            ->get();

        $data = [
            'warranty_total'    => $warrantyTotal,
            'warranty_rate'     => $warranty_rate,
            'warranty_hb_rate'  => $warranty_hb_rate,
            'roll_1_total'       => $roll_1_total,
            'roll_1_rate'       => $roll_1_rate,
            'roll_1_hb_rate'       => $roll_1_hb_rate,
            'roll_2_total'       => $roll_2_total,
            'roll_2_rate'       => $roll_2_rate,
            'roll_2_hb_rate'       => $roll_2_hb_rate,
            'roll_warranty_total'    => $roll_warranty_total,
            'roll_warranty_rate'    => $roll_warranty_rate,
            'roll_warranty_hb_rate'    => $roll_warranty_hb_rate,
            'distributions'    => $distributions,
            'year'             => date('Y'),
            'page'  => $this->getPage('质保数据统计', 'stat_warranty'),
            'film_type_id'     => $film_type_id,
        ];

        return view('admin.stat.warranty', $data);
    }

    /**
     * 获取环比日期
     *
     * @return array
     */
    public function getHbDate(): array
    {
        $hb_end_date = ((int)date('Y') - 1) . '-12';
        $hb_start_date = date('Y-m-d', strtotime($hb_end_date) - (strtotime(date('Y-m-d')) - strtotime(date("Y") . '-01')));
        return [
            'start_date' => $hb_start_date,
            'end_date'   => $hb_end_date
        ];
    }

    public function warrantyRollTypeStat(Request $request)
    {
        $year = $request->get('year');
        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';
        $dist_id = $request->input('dist_id');
        $dealer_id = $request->input('dealer_id');
        if ($dealer_id == $dist_id) {
            $dealer_id = 0;
        }

        $sql = 'SELECT count(distinct(w.id)) as total, fm.product_type_id, date_format(approved_date, "%Y-%m") as months
        FROM warranty AS w 
        LEFT JOIN users AS u ON w.user_id=u.id
        LEFT JOIN warranty_to_vehicle_coverage as wvc ON wvc.warranty_id=w.id
        LEFT JOIN film_type_master as fm ON fm.id=wvc.film_type_id
        WHERE approved_date>="' . $start_date . '"
        AND fm.product_type_id=1
        AND approved_date<="' . $end_date . '" AND approved=1 AND w.deleted_at IS NULL AND wvc.deleted_at IS NULL';
        if (!empty($dealer_id)) {
            $sql .= ' AND u.id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $dealer_ids = $this->userService->getDealers([$dist_id]);
            if (!empty($dealer_ids)) {
                $dealer_ids[] = $dist_id;
                $sql .= ' AND u.id IN(' . implode(',', $dealer_ids) . ')';
            }
        }

        $sql .= ' GROUP BY months';
        $result = DB::select($sql);
        if (empty($result)) {
            return response()->json([
                'code'  => 0,
                'list'  => []
            ]);
        }
        $roll_1_data = [];
        $monthsArr = [];
        foreach ($result as $item) {
            if (strtotime($item->months) > time()) {
                continue;
            }
            $monthsArr[] = $item->months;
            $month = $item->months;
            if (!isset($roll_1_data[$month])) {
                $roll_1_data[$month] = $item->total;
            } else {
                $roll_1_data[$month] += $item->total;
            }
        }

        $sql = 'SELECT count(distinct(w.id)) as total, fm.product_type_id, date_format(approved_date, "%Y-%m") as months
        FROM warranty AS w 
        LEFT JOIN users AS u ON w.user_id=u.id
        LEFT JOIN warranty_to_vehicle_coverage as wvc ON wvc.warranty_id=w.id
        LEFT JOIN film_type_master as fm ON fm.id=wvc.film_type_id
        WHERE approved_date>="' . $start_date . '"
        AND fm.product_type_id=2
        AND approved_date<="' . $end_date . '" AND approved=1 AND w.deleted_at IS NULL AND wvc.deleted_at IS NULL';
        if (!empty($dealer_id)) {
            $sql .= ' AND u.id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $dealer_ids = $this->userService->getDealers([$dist_id]);
            if (!empty($dealer_ids)) {
                $dealer_ids[] = $dist_id;
                $sql .= ' AND u.id IN(' . implode(',', $dealer_ids) . ')';
            }
        }

        $sql .= ' GROUP BY months';
        $result = DB::select($sql);

        $roll_2_data = [];
        $roll_2_month = [];
        foreach ($result as $item) {
            if (strtotime($item->months) > time()) {
                continue;
            }
            $month = $item->months;
            $roll_2_month[] = $month;
            if (!isset($roll_2_data[$month])) {
                $roll_2_data[$month] = $item->total;
            } else {
                $roll_2_data[$month] += $item->total;
            }
        }
        $month_result = array_diff($monthsArr, $roll_2_month);
        if (!empty($month_result)) {
            foreach ($month_result as $item) {
                $roll_2_data[$item] = 0;
            }
        }
        ksort($roll_2_data);

        $list = [
            [
                'name'  => '漆面膜',
                'data'  => array_values($roll_1_data),
            ],
            [
                'name'  => '窗膜',
                'data'  => array_values($roll_2_data),
            ],
        ];

        return response()->json([
            'code'  => 0,
            'list'  => $list
        ]);
    }

    public function warrantyPartStat(Request $request)
    {
        $year = $request->get('year');
        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';
        $dealer_id = $request->get('dealer_id');
        $dist_id = $request->get('dist_id');
        $sql = 'SELECT sum(part) as part_total, sum(full) as full_total, DATE_FORMAT(create_date,"%m") months FROM xpel_warranty_stat WHERE create_date>="' . $start_date . '" AND create_date<"' . $end_date . '"';
        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND dist_id=' . $dist_id;
        }

        $sql .= ' GROUP BY months';
        $result = DB::select($sql);

        if (empty($result)) {
            return response()->json([
                'code'  => 0,
                'list'  => []
            ]);
        }
        $part_data = [];
        $full_data = [];
        foreach ($result as $item) {
            $part_data[] = (int)$item->part_total;
            $full_data[] = (int)$item->full_total;
        }

        $list = [
            [
                'name'  => '局部',
                'data'  => $part_data,
            ],
            [
                'name'  => '全车',
                'data'  => $full_data
            ],
        ];

        return response()->json([
            'code'  => 0,
            'list'  => $list
        ]);
    }

    public function warrantyOrderStat(Request $request)
    {
        $year = $request->get('year');
        $start_date = $year . '-01';
        $end_date = ($year + 1) . '-01';
        $dealer_id = $request->get('dealer_id');
        $dist_id = $request->get('dist_id');
        $product_type_id = $request->get('product_type_id') ?? 1;
        if ($dealer_id == $dist_id) {
            $dealer_id = 0;
        }

        $sql = 'SELECT SUM(num) as total,  date_format(create_date, "%Y-%m") as months
        FROM xpel_warranty_roll_detail_stat AS w 
        LEFT JOIN users AS u ON w.dealer_id=u.id
        WHERE create_date>="' . $start_date . '"
        AND product_type_id=' . $product_type_id . '
        AND create_date<="' . $end_date . '"';
        if (!empty($dealer_id)) {
            $sql .= ' AND u.id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND w.dist_id=' . $dist_id;
        }

        $sql .= ' GROUP BY months';
        $result = DB::select($sql);

        $total = [];
        foreach ($result as $item) {
            if (strtotime($item->months) > time()) {
                continue;
            }
            $month = $item->months;
            if (!isset($total[$month])) {
                $total[$month] = (float)$item->total;
            } else {
                $total[$month] += (float)$item->total;
            }
        }

        $sql = 'SELECT SUM(num) AS order_total, create_month AS months FROM xpel_dealer_roll_stat WHERE create_month>="' . $start_date . '" AND create_month<="' . $end_date . '" AND product_type_id=' . $product_type_id;
        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND dealer_id=' . $dist_id;
        }
        if (empty($dealer_id) && empty($dist_id)) {
            $sql .= ' AND dealer_id=1';
        }
        $sql .= ' GROUP BY months';
        $orderResult = DB::select($sql);

        if (empty($result) && empty($orderResult)) {
            return response()->json([
                'code'  => 0,
                'list'  => []
            ]);
        }
        $order_total = [];
        foreach ($orderResult as $item) {
            $order_total[] = (float)$item->order_total;
        }

        $list = [
            [
                'name'  => '质保数量',
                'data'  => array_values($total),
            ],
            [
                'name'  => '订购卷数',
                'data'  => $order_total
            ],
        ];

        return response()->json([
            'code'  => 0,
            'list'  => $list
        ]);
    }

    public function showRollSales(Request $request)
    {
        $year = $request->input('year');
        $month = $request->input('month');
        $month = (int)$month + 1;

        $start_date = $year  . '-' . $month . '-01';
        $end_date = date('Y-m', strtotime($start_date . ' +1 month'));

        $sql = 'SELECT SUM(num) AS total, film_name as name, film_type_id FROM xpel_dealer_roll_stat WHERE create_month>="' . $start_date . '" AND create_month<"' . $end_date  . '" AND dealer_id=1 AND num>0 GROUP BY film_type_id ORDER BY total DESC LIMIT 30';
        $result = DB::select($sql);
        return response()->json([
            'code'  => 0,
            'list'  => $result ?: []
        ]);
    }

    private const STORE_TYPE_NAME = [
        '外星人店'      => 1,
        '授权店'       => 2,
        '直营店'       => 3,
        '旗舰店'       => 4,
        'ASP服务商'    => 5,
        '其他'         => 0,
    ];

    public function storeProvince(Request $request)
    {
        $name = $request->input('name') ?: '';
        $year = $request->input('year');
        $dist_id = $request->input('dist_id');
        $zsdj = $request->input('zsdj') ?: 'all';

        if (!empty($month)) {
            $start_date = $year  . '-' . $month . '-01';
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date  = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT abbr FROM users WHERE user_type=3 AND created_at<"' . $end_date . '" AND status=1 AND check_status=1 ';
        if (!empty($dist_id)) {
            $sql .= ' AND creator_id=' . $dist_id;
        }
        if ($zsdj !== 'all') {
            $sql .= ' AND zsdj=' . $zsdj;
        }
        $sql .= ' AND mdtype=' . self::STORE_TYPE_NAME[$name];
        $data = DB::select($sql);

        return response()->json([
            'code'  => 0,
            'list'  => $data ?: []
        ]);
    }

    public function showDistRolls(Request $request)
    {
        $name = $request->input('name') ?: '';
        $year = $request->input('year');
        $type = $request->input('type');
        $month = $request->input('month');

        if (!empty($month)) {
            $start_date = $year  . '-' . $month . '-01';
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date  = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $dist = Admin::where('abbr', $name)->first();
        if (empty($dist)) {
            return response()->json([
                'code'  => 0,
                'list'  => []
            ]);
        }

        $sql = 'SELECT film_name, sum(num) AS num FROM xpel_dealer_roll_stat WHERE create_month>="' . $start_date .  '" AND create_month<"' . $end_date . '" AND dealer_id=' . $dist->id . ' AND product_type_id=' . $type . ' GROUP BY film_type_id  ORDER BY num DESC';
        $data = DB::select($sql);

        return response()->json([
            'code'  => 0,
            'list'  => $data ?: []
        ]);
    }

    public function showDealerRolls(Request $request)
    {
        $name = $request->input('name') ?: '';
        $year = $request->input('year');
        $type = $request->input('type');
        $month = $request->input('month');

        if (!empty($month)) {
            $start_date = $year  . '-' . $month . '-01';
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date  = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $dist = Admin::where('abbr', $name)->first();
        if (empty($dist)) {
            return response()->json([
                'code'  => 0,
                'list'  => []
            ]);
        }
        $sql = 'SELECT film_name, sum(num) AS num FROM xpel_dealer_roll_stat WHERE create_month>="' . $start_date .  '" AND create_month<"' . $end_date . '" AND dealer_id=' . $dist->id . ' AND product_type_id=' . $type . ' GROUP BY film_type_id  ORDER BY num DESC';
        $data = DB::select($sql);

        return response()->json([
            'code'  => 0,
            'list'  => $data ?: []
        ]);
    }

    public function storePrice(Request $request): JsonResponse
    {
        $year = $request->get('year');
        $month = $request->get('month');
        $dist_id = $request->get('dist_id');
        $zsdj = $request->get('zsdj') ?? 'all';
        $mdtype = $request->get('mdtype') ?? 'all';
        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT
	SUM(xs.score) AS total_price,
	u.abbr,
    u.id
FROM
	xpel_warranty_roll_detail_stat AS xs 
	LEFT JOIN users AS u ON xs.dealer_id = u.id
WHERE
	u.user_type = 3
	AND xs.create_date>="%s" AND xs.create_date<"%s"
    AND u.id IN ( 
    SELECT dealer_id FROM xpel_dealer_stat  
    WHERE create_month >= "%s" 
	AND create_month <= "%s" AND price > 0)
    AND u.creator_id NOT IN (1515,1554,2784,2803,2815,2823,2835)
	';
        $sql = sprintf($sql, $start_date, $end_date, $start_date, $end_date);

        if (!empty($dist_id)) {
            $sql .= ' AND u.creator_id=' . $dist_id;
        }

        /* $sql = 'SELECT sum(price) as total_price, users.abbr, users.id FROM xpel_dealer_stat LEFT JOIN users ON xpel_dealer_stat.dealer_id=users.id WHERE create_month>="' . $start_date . '" AND create_month<"' . $end_date . '" ';
        if (!empty($dist_id)) {
            $sql .= ' AND creator_id="' . $dist_id . '"';
        }*/
        if ($zsdj !== 'all') {
            $sql .= ' AND u.zsdj=' . $zsdj;
        }
        if ($mdtype !== 'all') {
            $sql .= ' AND u.mdtype=' . $mdtype;
        }
        $sql .= ' GROUP BY u.id ORDER BY total_price DESC LIMIT 20';
        $dealers = DB::select($sql);

        $list = [];
        if (!empty($dealers) && is_array($dealers)) {
            foreach ($dealers as $dealer) {
                $list[] = [
                    $dealer->abbr,
                    (float)$dealer->total_price,
                    $dealer->id,
                ];
            }
        }

        return response()->json([
            'code'  => 1,
            'list'  => $list
        ]);
    }

    public function showWarrantyRolls(Request $request)
    {
        $year = $request->input('year');
        $month = $request->input('month');
        $type = $request->input('type') ?: 0;
        $type = (int)$type + 1;
        $month = array_flip(self::MONTH_CN)[$month] ?? 0;
        $dealer_id = $request->input('dealer_id');
        $dist_id = $request->input('dist_id');

        $start_date = $year  . '-' . $month . '-01';
        $end_date = date('Y-m', strtotime($start_date . ' +1 month'));

        $sql = 'SELECT count(distinct(w.id)) as total, fm.product_type_id, fm.simplified_chiness_value as name 
            FROM warranty AS w 
            LEFT JOIN users AS u ON w.user_id=u.id
            LEFT JOIN warranty_to_vehicle_coverage as wvc ON wvc.warranty_id=w.id
            LEFT JOIN film_type_master as fm ON fm.id=wvc.film_type_id
            WHERE approved_date>="%s"
            AND approved_date<"%s"
            AND approved=1
            AND fm.product_type_id=%d
            AND w.deleted_at IS NULL
            AND wvc.deleted_at IS NULL
        ';
        $sql = sprintf($sql, $start_date, $end_date, $type);
        if (!empty($dealer_id)) {
            $sql .= ' AND u.id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $dealer_ids = $this->userService->getDealers([$dist_id]);
            if (!empty($dealer_ids)) {
                $dealer_ids[] = $dist_id;
                $sql .= ' AND u.id IN(' . implode(',', $dealer_ids) . ')';
            }
        }

        $sql .= ' GROUP BY w.id ORDER BY total DESC';
        $result = DB::select($sql);

        $data = [];
        foreach ($result as $item) {
            if (empty($data[$item->name])) {
                $data[$item->name] = [
                    'name'  => $item->name,
                    'total' => 1,
                ];
            } else {
                $data[$item->name]['total'] += 1;
            }
        }
        $data = array_values($data);
        $column_key = array_column($data, 'total');
        array_multisort($column_key, SORT_DESC, $data);

        return response()->json([
            'code'  => 0,
            'list'  => $data ?: []
        ]);
    }

    /**
     * 获取最佳门店销售状况
     *
     * @return JsonResponse
     */
    public function storeSales(): JsonResponse
    {
        $list = $this->statService->storeSales($this->request['user_type']);

        return response()->json([
            'code'  => 0,
            'list'  => $list,
        ]);
    }

    /**
     * 获取最佳省代
     *
     * @return JsonResponse
     */
    public function storeScores(): JsonResponse
    {
        $list = $this->statService->scoreSales($this->request['user_type']);

        return response()->json([
            'code'  => 0,
            'list'  => $list,
        ]);
    }

    /**
     * 门店返点展示页
     *
     * @return mixed
     */
    public function price()
    {
        $sql = 'SELECT id, abbr FROM users WHERE status=1 AND user_type=2 AND deleted_at IS NULL AND id NOT IN(1515,1554,2784,2803,2815,2823,2835) ORDER BY status, unique_id ASC';
        $distributions = DB::select($sql);

        $data = [
            'distributions' => $distributions,
            'page' => $this->getPage('门店返点统计', 'stat_price'),
        ];

        return view('admin.stat.price', $data);
    }

    public function rollAll()
    {
        $startYear = date('Y') . '-01';
        $sql = 'SELECT sum(sale_1) as sale_sum_1, sum(sale_2) as sale_sum_2, sum(sale_3) as sale_sum_3, 
        sum(transfer_1) as transfer_sum_1, sum(transfer_2) as transfer_sum_2, sum(transfer_3) as transfer_sum_3,
        sum(return_1) as return_sum_1, sum(return_2) as return_sum_2, sum(return_3) as return_sum_3,
        sum(stock_1) as stock_sum_1, sum(stock_2) as stock_sum_2, sum(stock_3) as stock_sum_3
        FROM xpel_roll_stat WHERE create_date>="' . $startYear . '"';
        $yearData = DB::select($sql);
        $saleQmTotal = ($yearData[0]->sale_sum_1 ?? 0);
        $saleWinTotal = ($yearData[0]->sale_sum_2 ?? 0);
        $transferQmTotal = ($yearData[0]->transfer_sum_1 ?? 0);
        $transferWinTotal = $yearData[0]->transfer_sum_2 ?? 0;
        $returnQmTotal = $yearData[0]->return_sum_1 ?? 0;
        $returnWinTotal = $yearData[0]->return_sum_2 ?? 0;

        $lastYear = (int)date('Y') - 1;
        $sql = 'SELECT sum(sale_1) as sale_sum_1, sum(sale_2) as sale_sum_2, sum(sale_3) as sale_sum_3, 
        sum(transfer_1) as transfer_sum_1, sum(transfer_2) as transfer_sum_2, sum(transfer_3) as transfer_sum_3,
        sum(return_1) as return_sum_1, sum(return_2) as return_sum_2, sum(return_3) as return_sum_3,
        sum(stock_1) as stock_sum_1, sum(stock_2) as stock_sum_2, sum(stock_3) as stock_sum_3
        FROM xpel_roll_stat WHERE create_date>="' . $lastYear . '-01' . '" AND create_date<"' . $lastYear . '-' . date('m-d')  . '"';
        $lastYearData = DB::select($sql);
        $saleQmRate = $saleQmTotal > 0 ? (($lastYearData[0]->sale_sum_1 ?? 0) - $saleQmTotal) / $saleQmTotal : 0;
        $transferQmRate = $transferQmTotal > 0 ? (($lastYearData[0]->transfer_sum_1 ?? 0) - $transferQmTotal) / $transferQmTotal : 0;
        $returnQmRate = $returnQmTotal > 0 ? (($lastYearData[0]->return_sum_1 ?? 0) - $returnQmTotal) / $returnQmTotal : 0;
        $saleWinRate = $saleWinTotal > 0 ? (($lastYearData[0]->sale_sum_2 ?? 0) - $saleWinTotal) / $saleWinTotal : 0;
        $transferWinRate = $transferWinTotal > 0 ? (($lastYearData[0]->transfer_sum_2 ?? 0) - $transferWinTotal) / $transferWinTotal : 0;
        $returnWinRate = $returnWinTotal > 0 ? (($lastYearData[0]->return_sum_2 ?? 0) - $returnWinTotal) / $returnWinTotal : 0;

        $sql = 'SELECT sum(sale_1) as sale_sum_1, sum(sale_2) as sale_sum_2, sum(sale_3) as sale_sum_3, 
        sum(transfer_1) as transfer_sum_1, sum(transfer_2) as transfer_sum_2, sum(transfer_3) as transfer_sum_3,
        sum(return_1) as return_sum_1, sum(return_2) as return_sum_2, sum(return_3) as return_sum_3,
        sum(stock_1) as stock_sum_1, sum(stock_2) as stock_sum_2, sum(stock_3) as stock_sum_3
        FROM xpel_roll_stat WHERE create_date>="' . $this->getHbDate()['start_date'] . '" AND create_date<"' . $this->getHbDate()['end_date'] . '"';
        $hbData = DB::select($sql);
        $saleQmHbRate = $saleQmTotal > 0 ? (($hbData[0]->sale_sum_1 ?? 0) - $saleQmTotal) / $saleQmTotal : 0;
        $transferQmHbRate = $transferQmTotal > 0 ? (($hbData[0]->transfer_sum_1 ?? 0) - $transferQmTotal) / $transferQmTotal : 0;
        $returnQmHbRate = $returnQmTotal > 0 ? (($hbData[0]->return_sum_1 ?? 0) - $returnQmTotal) / $returnQmTotal : 0;
        $saleWinHbRate = $saleWinTotal > 0 ? (($hbData[0]->sale_sum_2 ?? 0) - $saleWinTotal) / $saleWinTotal : 0;
        $transferWinHbRate = $transferWinTotal > 0 ? (($hbData[0]->transfer_sum_2 ?? 0) - $transferWinTotal) / $transferWinTotal : 0;
        $returnWinHbRate = $returnWinTotal > 0 ? (($hbData[0]->return_sum_2 ?? 0) - $returnWinTotal) / $returnWinTotal : 0;

        $data = [
            'sale_qm_total'    => round($saleQmTotal, 2),
            'sale_win_total'    => round($saleWinTotal, 2),
            'sale_qm_rate'     => round($saleQmRate * 100,  2),
            'sale_win_rate'    => round($saleWinRate * 100,  2),
            'sale_hb_qm_rate'    => round($saleQmHbRate * 100, 2),
            'sale_hb_win_rate'     => round($saleWinHbRate * 100, 2),
            'trans_qm_total'    => round($transferQmTotal, 2),
            'trans_win_total'    => round($transferWinTotal, 2),
            'trans_qm_rate'     => round($transferQmRate * 100,  2),
            'trans_win_rate'    => round($transferWinRate * 100,  2),
            'trans_hb_qm_rate'    => round($transferQmHbRate * 100, 2),
            'trans_hb_win_rate'     => round($transferWinHbRate * 100, 2),
            'return_qm_total'    => round($returnQmTotal, 2),
            'return_win_total'    => round($returnWinTotal, 2),
            'return_qm_rate'     => round($returnQmRate * 100,  2),
            'return_win_rate'    => round($returnWinRate * 100,  2),
            'return_hb_qm_rate'    => round($returnQmHbRate * 100, 2),
            'return_hb_win_rate'     => round($returnWinHbRate * 100, 2),
        ];

        return response()->json([
            'code'  => 0,
            'list'  => $data,
        ]);
    }



    public function singleRollDetail(Request $request)
    {
        $year = $request->input('year');
        $month = $request->input('month');
        $type = $request->input("type");
        $dealer_id = $request->input('dealer_id');
        $dist_id = $request->input('dist_id');

        if (!empty($month)) {
            $start_date = $year  . '-' . $month . '-01';
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date  = $year . '-01';
            $end_date = ($year + 1) . '-12';
        }

        $sql = 'SELECT sum(num) AS total, film_name as name, film_type_id FROM xpel_dealer_roll_stat WHERE create_month>="' . $start_date . '" AND create_month<="' . $end_date  . '" AND product_type_id=' . $type;

        if (empty($dealer_id) && empty($dist_id)) {
            $sql .= " AND dealer_id=1";
        }

        if (!empty($dealer_id)) {
            $sql .= ' AND dealer_id=' . $dealer_id;
        }
        if (!empty($dist_id)) {
            $sql .= ' AND dealer_id=' . $dist_id;
        }

        $sql .= ' GROUP BY film_type_id ORDER BY total DESC';
        $result = DB::select($sql);

        if (empty($result)) {
            return [
                'code'  => 0,
                'list'  => []
            ];
        }

        return response()->json([
            'code'  => 1,
            'list'  => $result,
        ]);
    }

    public function countryTask(Request $request)
    {
        $year = $request->input('year');
        $month = $request->input('month');

        if (!empty($month)) {
            $start_date = $year  . '-' . $month . '-01';
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date  = $year . '-01';
            $end_date = $year . '-12';
        }

        $sql = 'SELECT SUM(qm_score) AS total_qm_score, SUM(win_score) AS total_win_score, create_month FROM xpel_dealer_stat WHERE create_month>="%s" AND create_month<="%s" AND dealer_id=1 GROUP BY create_month';
        $sql = sprintf($sql, $start_date, $end_date);
        $data = DB::select($sql);

        $list = [];

        if (!empty($data)) {
            foreach ($data as $item) {
                $list[] = round($item->total_qm_score + $item->total_win_score, 2);
            }
        }

        return response()->json([
            'code'  => 1,
            'list'  => [
                [
                    'name'  => $year,
                    'data'  => $list
                ],
            ]
        ]);
    }

    public function showCountryTaskDetail(Request $request)
    {
        $year = $request->input('year');
        $month = $request->input('month');

        if (!empty($month)) {
            $start_date = $year  . '-' . $month . '-01';
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date  = $year . '-01';
            $end_date = $year . '-12';
        }

        $sql = 'SELECT film_name, sum(score) AS score FROM xpel_dealer_roll_stat WHERE create_month>="' . $start_date .  '" AND create_month<"' . $end_date . '" AND dealer_id=1 AND score>0 GROUP BY film_type_id  ORDER BY score DESC';
        $sql = sprintf($sql, $start_date, $end_date);
        $data = DB::select($sql);

        $list = [];
        if (!empty($data)) {
            foreach ($data as $item) {
                $result = [
                    'total_score' => $item->score,
                    'abbr_name'   => $item->film_name,
                ];
                $list[] = $result;
            }
        }

        return [
            'code'  => 1,
            'list'  => $list
        ];
    }

    public function rollStockAll()
    {
        $type = $this->request->input('type');
        $data = $this->statService->rollStockAll($type);

        return response()->json([
            'code'  => 0,
            'list'  => $data,
        ]);
    }

    public function rollReturn()
    {
        $type = $this->request->input('type');
        $data = $this->statService->rollReturn($type);

        return response()->json([
            'code'  => 0,
            'list'  => $data,
        ]);
    }

    public function rollTransfer()
    {
        $type = $this->request->input('type');
        $data = $this->statService->rollTransfer($type);

        return response()->json([
            'code'  => 0,
            'list'  => $data,
        ]);
    }

    public function rollSale()
    {
        $type = $this->request->input('type');
        $data = $this->statService->rollSale($type);

        return response()->json([
            'code'  => 0,
            'list'  => $data,
        ]);
    }

    public function dealerPrice(Request $request)
    {
        $year = $request->get('year');
        $month = $request->get('month');
        $dist_id = $request->get('dist_id');
        $name = $request->get('name') ?? '';

        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        } else {
            $start_date = $year . '-01';
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT SUM(score) AS total_price, create_date AS create_month FROM xpel_warranty_roll_detail_stat LEFT JOIN users ON xpel_warranty_roll_detail_stat.dealer_id=users.id WHERE create_date>="' . $start_date . '" AND create_date<"' . $end_date . '"';
        if (!empty($dist_id)) {
            $sql .= ' AND creator_id="' . $dist_id . '"';
        }
        if (!empty($name)) {
            $sql .= ' AND users.abbr="' . $name . '"';
        }
        $sql .= ' GROUP BY create_month ORDER BY create_month ASC';
        $list = DB::select($sql);


        return response()->json([
            'code'  => 1,
            'list'  => $list ?? []
        ]);
    }

    public function detailStat(Request $request)
    {
        $user_type = session('admin.user_type');
        $user_id = session('admin.id');

        $menus = (new ModuleService())->getWebButton(session('admin.role_id') ?? 0, $request->path());
        if ($user_type == 4) {
            $distribution_ids = $this->userService->getDistributions(4, $user_id);
        }
        if ($user_type == 2) {
            $distribution_ids = [$user_id];
        }


        $distributions = $this->getDistributions();

        $allDistributions = [];
        foreach ($distributions as $item) {
            if (!empty($distribution_ids)) {
                if (in_array($item->id, $distribution_ids)) {
                    $allDistributions[] = $item;
                }
            } else {
                $allDistributions[] = $item;
            }
        }

        $data = [
            'distributions' => $allDistributions,
            'page'          => $this->getPage('质保详情统计', 'stat_detail'),
            'year'          => date('Y'),
            'menus'         => $menus ?? [],
            'user_type'     => $user_type,
            'distribution_ids' => $distribution_ids ?? [],
        ];

        return view('admin.stat.detail', $data);
    }

    public function detailDistStat(Request $request)
    {
        $year = $request->get('year');
        $month = $request->get('month');
        $type = $request->get('type');
        $page = $request->get('page');
        $all_dist_id = $request->get("all_dist_id");
        $start_date = $request->input('start_date') ?: '';
        $end_date = $request->input('end_date') ?: '';
        if (empty($year)) {
            $year = date('Y') - 1;
        }

        if (!empty($end_date)) {
            $end_date = date('Y-m-d', strtotime($end_date) + 86400);
        }
      
        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        }
        if (empty($month) && empty($start_date)) {
            $start_date = $year . '-01';
        }
        if (empty($month) && empty($end_date)) {
            $end_date = ($year + 1) . '-01';
        }

        if ($start_date == $end_date) {
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT SUM(total) as total_score, users.abbr FROM xpel_warranty_roll_stat LEFT JOIN users ON xpel_warranty_roll_stat.dist_id=users.id WHERE create_date>="' . $start_date . '" AND create_date<"' . $end_date . '" AND product_type_id=' . $type;;
        if (!empty($all_dist_id)) {
            $sql .= ' AND dist_id IN(' . $all_dist_id . ')';
        }
        $sql .= ' GROUP BY dist_id ORDER BY total_score DESC';
        if (empty($page) || $page !== 'all') {
            $sql .= ' LIMIT 15';
        }
        $dealers = DB::select($sql);

        $list = [];
        if (!empty($dealers) && is_array($dealers)) {
            foreach ($dealers as $dealer) {
                $list[] = [
                    'name' => $dealer->abbr,
                    'y' => round($dealer->total_score, 2),
                ];
            }
        }

        return response()->json([
            'code'  => 1,
            'list'  => $list
        ]);
    }

    public function detailDealerStat(Request $request)
    {
        $year = $request->get('year');
        $month = $request->get('month');
        $type = $request->get('type');
        $page = $request->get('page');
        $dist_id = $request->get('dist_id');
        $city_id = $request->get('city_id');
        $date = $request->get('date');
        $all_dist_id = $request->get("all_dist_id");
        $start_date = $request->input('start_date');
        $end_date = $request->input('end_date');
        if (empty($year)) {
            $year = date('Y') - 1;
        }
        if (!empty($end_date)) {
            $end_date = date('Y-m-d', strtotime($end_date) + 86400);
        }
        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        }
        if (empty($month) && empty($start_date)) {
            $start_date = $year . '-01';
        }
        if (empty($month) && empty($end_date)) {
            $end_date = ($year + 1) . '-01';
        }
        if ($start_date == $end_date) {
            $end_date = ($year + 1) . '-01';
        }

        $sql = 'SELECT SUM(total) as total_score, users.abbr FROM xpel_warranty_roll_stat LEFT JOIN users ON xpel_warranty_roll_stat.dealer_id=users.id WHERE create_date>="' . $start_date . '" AND create_date<"' . $end_date . '" AND product_type_id=' . $type . ' AND dealer_id!=dist_id ';
        if (!empty($dist_id)) {
            $sql .= ' AND dist_id=' . $dist_id;
        }
        if (!empty($all_dist_id)) {
            $sql .= ' AND dist_id IN (' . $all_dist_id . ')';
        }
        if (!empty($city_id)) {
            $sql .= ' AND city=' . $city_id;
        }
        $sql .= ' GROUP BY dealer_id ORDER BY total_score DESC';
        if (empty($page) || $page !== 'all') {
            $sql .= ' LIMIT 15';
        }
        $dealers = DB::select($sql);

        $list = [];
        if (!empty($dealers) && is_array($dealers)) {
            foreach ($dealers as $dealer) {
                $list[] = [
                    'name' => $dealer->abbr,
                    'y' => round($dealer->total_score, 2),
                ];
            }
        }

        if (!empty($dist_id)) {
            $distInfo = Admin::where('id', $dist_id)->first();
        }

        return response()->json([
            'code'  => 1,
            'list'  => $list,
            'dist_info' => !empty($distInfo) ? $distInfo->abbr : '',
        ]);
    }

    public function showDistWarrantyDetail(Request $request)
    {
        $name = $request->input('name') ?: '';
        $year = $request->input('year');
        $type = $request->input('type');
        $month = $request->input('month');
        $start_date = $request->input('start_date');
        $end_date = $request->input('end_date');
        if (empty($year)) {
            $year = date('Y') - 1;
        }
        if (!empty($end_date)) {
            $end_date = date('Y-m-d', strtotime($end_date) + 86400);
        }
        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        }
        if (empty($month) && empty($start_date)) {
            $start_date = $year . '-01';
        }
        if (empty($month) && empty($end_date)) {
            $end_date = ($year + 1) . '-01';
        }

        if ($start_date == $end_date) {
            $end_date = ($year + 1) . '-01';
        }

        $dist = Admin::where('abbr', $name)->first();
        if (empty($dist)) {
            return response()->json([
                'code'  => 0,
                'list'  => []
            ]);
        }

        $sql = 'SELECT create_date as film_name, sum(total) AS num FROM xpel_warranty_roll_stat left join film_type_master on xpel_warranty_roll_stat.roll_type_id=film_type_master.id WHERE xpel_warranty_roll_stat.create_date>="' . $start_date .  '" AND create_date<"' . $end_date . '" AND dist_id=' . $dist->id . ' AND xpel_warranty_roll_stat.product_type_id=' . $type . ' GROUP BY create_date  ORDER BY create_date ASC';
        $data = DB::select($sql);

        return response()->json([
            'code'  => 0,
            'list'  => $data ?: []
        ]);
    }

    public function showDealerWarrantyDetail(Request $request)
    {
        $name = $request->input('name') ?: '';
        $year = $request->input('year');
        $type = $request->input('type');
        $month = $request->input('month');
        $dist_id = $request->input('dist_id');
        $start_date = $request->input('start_date');
        $end_date = $request->input('end_date');
        if (empty($year)) {
            $year = date('Y') - 1;
        }
        if (!empty($end_date)) {
            $end_date = date('Y-m-d', strtotime($end_date) + 86400);
        }
        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        }
        if (empty($month) && empty($start_date)) {
            $start_date = $year . '-01';
        }
        if (empty($month) && empty($end_date)) {
            $end_date = ($year + 1) . '-01';
        }

        if ($start_date == $end_date) {
            $end_date = ($year + 1) . '-01';
        }

        $dist = Admin::where('abbr', $name)->first();
        if (empty($dist)) {
            return response()->json([
                'code'  => 0,
                'list'  => []
            ]);
        }

        $sql = 'SELECT create_date as film_name, sum(total) AS num FROM xpel_warranty_roll_stat left join film_type_master on xpel_warranty_roll_stat.roll_type_id=film_type_master.id WHERE xpel_warranty_roll_stat.create_date>="' . $start_date .  '" AND create_date<"' . $end_date . '" AND dealer_id=' . $dist->id . ' AND xpel_warranty_roll_stat.product_type_id=' . $type . ' GROUP BY create_date  ORDER BY create_date ASC';
        $data = DB::select($sql);

        return response()->json([
            'code'  => 0,
            'list'  => $data ?: [],
        ]);
    }

    public function detailDealerScoreStat(Request $request)
    {
        $user_type = session('admin.user_type');
        $user_id = session('admin.id');
        $year = $request->get('year');
        $month = $request->get('month');
        $type = $request->get('type');
        $page = $request->get('page');
        $dist_id = $request->get('dist_id');
        $city_id = $request->get('city_id');
        $date = $request->get('date');
        $all_dist_id = $request->get("all_dist_id");
        $start_date = $request->input('start_date');
        $end_date = $request->input('end_date');
        $product_type_id = $request->input('product_type_id') ?: 1;
        if (empty($year)) {
            $year = date('Y') - 1;
        }
        if (!empty($end_date)) {
            $end_date = date('Y-m-d', strtotime($end_date) + 86400);
        }

        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        }
        if (empty($month) && empty($start_date)) {
            $start_date = $year . '-01';
        }
        if (empty($month) && empty($end_date)) {
            $end_date = ($year + 1) . '-01';
        }
        if ($start_date == $end_date) {
            $end_date = ($year + 1) . '-01';
        }

        if ($user_type == 2) {
            $dist_id = $user_id;
        }

        if ($type == 1) {
            $sql = 'SELECT SUM(score) as total_score, users.abbr FROM xpel_warranty_roll_detail_stat LEFT JOIN users ON xpel_warranty_roll_detail_stat.dealer_id=users.id WHERE create_date>="' . $start_date . '" AND create_date<"' . $end_date . '" AND product_type_id=' . $product_type_id;
            if (!empty($dist_id)) {
                $sql .= ' AND dist_id=' . $dist_id;
            }
            $sql .= ' AND dealer_id!=dist_id';
            if (!empty($all_dist_id)) {
                $sql .= ' AND dist_id IN(' . $all_dist_id . ')';
            }
            if (!empty($city_id)) {
                $sql .= ' AND users.city=' . $city_id;
            }
            $sql .= ' GROUP BY dealer_id ORDER BY total_score DESC';
        } else {
            $sql = 'SELECT SUM(score) as total_score, users.abbr FROM xpel_warranty_roll_detail_stat LEFT JOIN users ON xpel_warranty_roll_detail_stat.dist_id=users.id WHERE create_date>="' . $start_date . '" AND create_date<"' . $end_date . '" AND product_type_id=' . $product_type_id;
            if (!empty($all_dist_id)) {
                $sql .= ' AND dist_id IN(' . $all_dist_id . ')';
            }
            $sql .= ' GROUP BY dist_id ORDER BY total_score DESC';
        }

        if (empty($page) || $page !== 'all') {
            $sql .= ' LIMIT 15';
        }
        $dealers = DB::select($sql);

        $list = [];
        if (!empty($dealers) && is_array($dealers)) {
            foreach ($dealers as $dealer) {
                $list[] = [
                    'name' => $dealer->abbr,
                    'y' => round($dealer->total_score, 2),
                ];
            }
        }

        if (!empty($dist_id)) {
            $distInfo = Admin::where('id', $dist_id)->first();
        }

        return response()->json([
            'code'  => 1,
            'list'  => $list,
            'dist_info' => !empty($distInfo) ? $distInfo->abbr : '',
        ]);
    }

    public function showDetailDealerScoreStat(Request $request)
    {
        $name = $request->input('name') ?: '';
        $year = $request->input('year');
        $type = $request->input('type');
        $month = $request->input('month');
        $dist_id = $request->input('dist_id');
        $start_date = $request->input('start_date');
        $end_date = $request->input('end_date');
        $product_type_id = $request->input('product_type_id') ?? 1;
        if (empty($year)) {
            $year = date('Y') - 1;
        }
        if (!empty($month)) {
            $start_date = $year . '-' . $month;
            $end_date = date('Y-m', strtotime($start_date . ' +1 month'));
        }
        if (empty($month) && empty($start_date)) {
            $start_date = $year . '-01';
        }
        if (empty($month) && empty($end_date)) {
            $end_date = ($year + 1) . '-01';
        }
        if ($start_date == $end_date) {
            $end_date = ($year + 1) . '-01';
        }

        $dist = Admin::where('abbr', $name)->first();
        if (empty($dist)) {
            return response()->json([
                'code'  => 0,
                'list'  => []
            ]);
        }

        if ($type == 2) {
            $sql = 'SELECT simplified_chiness_value as film_name, sum(score) AS num FROM xpel_warranty_roll_detail_stat left join film_type_master on xpel_warranty_roll_detail_stat.film_type_id=film_type_master.id WHERE xpel_warranty_roll_detail_stat.create_date>="' . $start_date .  '" AND create_date<"' . $end_date . '" AND dist_id=' . $dist->id . ' AND xpel_warranty_roll_detail_stat.product_type_id=' . $product_type_id . ' GROUP BY xpel_warranty_roll_detail_stat.film_type_id  ORDER BY num DESC';
        } else {
            $sql = 'SELECT simplified_chiness_value as film_name, sum(score) AS num FROM xpel_warranty_roll_detail_stat left join film_type_master on xpel_warranty_roll_detail_stat.film_type_id=film_type_master.id WHERE xpel_warranty_roll_detail_stat.create_date>="' . $start_date .  '" AND create_date<"' . $end_date . '" AND dealer_id=' . $dist->id . ' AND xpel_warranty_roll_detail_stat.product_type_id=' . $product_type_id . ' GROUP BY xpel_warranty_roll_detail_stat.film_type_id  ORDER BY num DESC';
        }
        $data = DB::select($sql);

        return response()->json([
            'code'  => 0,
            'list'  => $data ?: [],
        ]);
    }

    /**
     * 获取数据
     *
     * @param Request $request
     * @return array|JsonResponse
     */
    public function getCities(Request $request)
    {
        $dist_id = $request->get('dist_id');
        $dist = Admin::where('id', $dist_id)->first();
        if (empty($dist)) {
            return [];
        }

        $provinceId = $dist->province_id;
        $result = DB::table('cities')->where('province_id', $provinceId)->get();
        if (empty($result)) {
            return [];
        }

        return response()->json([
            'code'  => 0,
            'list'  => $result,
        ]);
    }
}
